﻿using HCQ2_Common;
using HCQ2_IDAL;
using HCQ2_Model.DocModel;
using HCQ2_Model.DocModel.APIModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace HCQ2_DAL_MSSQL
{
    public partial class T_DocEmpowerDAL: IT_DocEmpowerDAL
    {
        /// <summary>
        ///  获取文档授权列表
        /// </summary>
        /// <param name="powerName"></param>
        /// <param name="startDate"></param>
        /// <param name="endDate"></param>
        /// <param name="powerStatus"></param>
        /// <param name="page"></param>
        /// <param name="rows"></param>
        /// <returns></returns>
        public List<EmpowerListModel> GetPowerListData(string powerName, string startDate, string endDate, int powerStatus, int page, int rows)
        {
            sb?.Clear();
            sb.AppendFormat(@"SELECT TOP {0} power.*,users.user_name,users.login_name,users.user_phone,users.user_email FROM 
(SELECT user_id,user_name,login_name,user_phone,user_email FROM dbo.T_User ",rows);
            if (!string.IsNullOrEmpty(powerName))
                sb.AppendFormat(" WHERE user_name like '%{0}%' or login_name like '%{0}%' ", powerName);
            sb.AppendFormat(@")users INNER JOIN
(SELECT ROW_NUMBER() OVER(ORDER BY id ASC)AS rowNumber,id,user_id,status,num_code,CONVERT(varchar(100),start_date,120) AS start_date,CONVERT(varchar(100),end_date,120) AS end_date,reg_name,doc_note,machine_code FROM dbo.T_DocEmpower WHERE 1=1 ");
            //if (!string.IsNullOrEmpty(powerName))
            //    sb.AppendFormat(" AND num_code like '%{0}%' ", powerName);
            if(powerStatus>-1)
                sb.AppendFormat(" AND status={0}", powerStatus);
            //日期
            if (!string.IsNullOrEmpty(startDate) && !string.IsNullOrEmpty(endDate))
                sb.AppendFormat(" AND '{0}'<=CONVERT(varchar(100),start_date,23) AND '{1}'>=CONVERT(varchar(100),end_date,23) ", startDate, endDate);
            else if (!string.IsNullOrEmpty(startDate) && string.IsNullOrEmpty(endDate))
                sb.AppendFormat(" AND '{0}'<=CONVERT(varchar(100),start_date,23) ", startDate);
            else if (string.IsNullOrEmpty(startDate) && !string.IsNullOrEmpty(endDate))
                sb.AppendFormat(" AND '{0}'>=CONVERT(varchar(100),end_date,23) ", endDate);
            sb.AppendFormat(@")power ON power.user_id = users.user_id WHERE power.rowNumber>{0};", (page - 1) * rows);
            DataTable dt = HCQ2_Common.SQL.SqlHelper.ExecuteDataTable(sb.ToString());
            return HCQ2_Common.Data.DataTableHelper.DataTableToIList<EmpowerListModel>(dt, true);
        }
        /// <summary>
        ///  获取文档授权列表数量
        /// </summary>
        /// <param name="powerName"></param>
        /// <param name="startDate"></param>
        /// <param name="endDate"></param>
        /// <param name="powerStatus"></param>
        /// <param name="page"></param>
        /// <param name="rows"></param>
        /// <returns></returns>
        public int GetPowerDataCount(string powerName, string startDate, string endDate, int powerStatus, int page, int rows)
        {
            sb?.Clear();
            sb.AppendFormat(@"SELECT COUNT(0) FROM
(SELECT user_id FROM dbo.T_User ");
            if (!string.IsNullOrEmpty(powerName))
                sb.AppendFormat(" WHERE user_name like '%{0}%' or login_name like '%{0}%' ", powerName);
            sb.AppendFormat(@")users INNER JOIN
(SELECT user_id FROM dbo.T_DocEmpower WHERE 1=1 ");
            //if (!string.IsNullOrEmpty(powerName))
            //    sb.AppendFormat(" AND num_code like '%{0}%' ", powerName);
            if (powerStatus > -1)
                sb.AppendFormat(" AND status={0}", powerStatus);
            //日期
            if (!string.IsNullOrEmpty(startDate) && !string.IsNullOrEmpty(endDate))
                sb.AppendFormat(" AND '{0}'<=CONVERT(varchar(100),start_date,23) AND '{1}'>=CONVERT(varchar(100),end_date,23) ", startDate, endDate);
            else if (!string.IsNullOrEmpty(startDate) && string.IsNullOrEmpty(endDate))
                sb.AppendFormat(" AND '{0}'<=CONVERT(varchar(100),start_date,23) ", startDate);
            else if (string.IsNullOrEmpty(startDate) && !string.IsNullOrEmpty(endDate))
                sb.AppendFormat(" AND '{0}'>=CONVERT(varchar(100),end_date,23) ", endDate);
            sb.AppendFormat(@")power ON power.user_id = users.user_id;");
            return Helper.ToInt(HCQ2_Common.SQL.SqlHelper.ExecuteScalar(sb.ToString()));
        }


        //**********************************************************接口**********************************************************
        /// <summary>
        ///  判断用户是否授权
        /// </summary>
        /// <param name="user_id"></param>
        /// <returns></returns>
        public bool GetPowerCountByUser(int user_id)
        {
            if (user_id <= 0)
                return false;
            sb?.Clear();
            sb.Append($@"SELECT COUNT(0) AS num FROM (SELECT MAX(end_date) AS end_date,user_id FROM T_DocEmpower GROUP BY user_id)c WHERE c.user_id={user_id} AND c.end_date<GETDATE()");
            int loginCount = Helper.ToInt(HCQ2_Common.SQL.SqlHelper.ExecuteScalar(sb.ToString()));
            return loginCount == 0 ? true : false;
        }
        /// <summary>
        ///  获取授权列表
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        public List<EmpowerAPPModel> GetPowerAPPList(EmpowerAPPParam param,int user_id)
        {
            sb?.Clear();
            sb.AppendFormat(@"SELECT TOP {0} id,num_code,start_date,end_date,machine_code,machine_name,(CASE WHEN due_day>=0 AND due_day<=10 THEN 2 WHEN due_day<0 THEN 4 ELSE status END) AS status FROM (SELECT id,num_code,CONVERT(varchar(100),start_date,23) AS start_date,CONVERT(varchar(100),end_date,23) AS end_date,
                machine_code,machine_name,status,DATEDIFF(DAY,GETDATE(),end_date) AS due_day,ROW_NUMBER() OVER(ORDER BY id) AS rowNumber FROM dbo.T_DocEmpower WHERE user_id={1} ", param.rows,user_id);
            if (!string.IsNullOrEmpty(param.num_code))
                sb.AppendFormat(" AND num_code LIKE '%{0}%' ", param.num_code);
            if (!string.IsNullOrEmpty(param.machine_code))
                sb.AppendFormat(" AND machine_code LIKE '%{0}%' ", param.machine_code);
            //状态
            if (param.status > -1)
            {
                switch (param.status)
                {
                    case 2: sb.AppendFormat(" AND DATEDIFF(DAY,GETDATE(),end_date)<=10 AND DATEDIFF(DAY,GETDATE(),end_date)>=0 "); break;//10天内到期
                    case 4: sb.AppendFormat(" AND DATEDIFF(DAY,GETDATE(),end_date)<0 "); break;//逾期
                    default: sb.AppendFormat(" AND status={0} ",param.status); break;//其他状态
                }
            }
            //日期
            if (!string.IsNullOrEmpty(param.start_date) && !string.IsNullOrEmpty(param.end_date))
                sb.AppendFormat(" AND '{0}'<=CONVERT(varchar(100),start_date,23) AND '{1}'>=CONVERT(varchar(100),end_date,23) ", param.start_date, param.end_date);
            else if (!string.IsNullOrEmpty(param.start_date) && string.IsNullOrEmpty(param.end_date))
                sb.AppendFormat(" AND '{0}'<=CONVERT(varchar(100),start_date,23) ", param.start_date);
            else if (string.IsNullOrEmpty(param.start_date) && !string.IsNullOrEmpty(param.end_date))
                sb.AppendFormat(" AND '{0}'>=CONVERT(varchar(100),end_date,23) ", param.end_date);
            sb.AppendFormat(@")power WHERE power.rowNumber>{0};", (param.page - 1) * param.rows);
            DataTable dt = HCQ2_Common.SQL.SqlHelper.ExecuteDataTable(sb.ToString());
            return HCQ2_Common.Data.DataTableHelper.DataTableToIList<EmpowerAPPModel>(dt, true);
        }
        /// <summary>
        ///  获取授权列表数量
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        public int GetPowerAPPCount(EmpowerAPPParam param, int user_id)
        {
            sb?.Clear();
            sb.AppendFormat("SELECT COUNT(0) AS num FROM dbo.T_DocEmpower WHERE user_id={0} ",user_id);
            if (!string.IsNullOrEmpty(param.num_code))
                sb.AppendFormat(" AND num_code LIKE '%{0}%' ", param.num_code);
            if (!string.IsNullOrEmpty(param.machine_code))
                sb.AppendFormat(" AND machine_code LIKE '%{0}%' ", param.machine_code);
            //状态
            if (param.status > -1)
            {
                switch (param.status)
                {
                    case 2: sb.AppendFormat(" AND DATEDIFF(DAY,GETDATE(),end_date)<=10 AND DATEDIFF(DAY,GETDATE(),end_date)>=0 "); break;//10天内到期
                    case 4: sb.AppendFormat(" AND DATEDIFF(DAY,GETDATE(),end_date)<0 "); break;//逾期
                    default: sb.AppendFormat(" AND status={0} ", param.status); break;//其他状态
                }
            }
            //日期
            if (!string.IsNullOrEmpty(param.start_date) && !string.IsNullOrEmpty(param.end_date))
                sb.AppendFormat(" AND '{0}'<=CONVERT(varchar(100),start_date,23) AND '{1}'>=CONVERT(varchar(100),end_date,23) ", param.start_date, param.end_date);
            else if (!string.IsNullOrEmpty(param.start_date) && string.IsNullOrEmpty(param.end_date))
                sb.AppendFormat(" AND '{0}'<=CONVERT(varchar(100),start_date,23) ", param.start_date);
            else if (string.IsNullOrEmpty(param.start_date) && !string.IsNullOrEmpty(param.end_date))
                sb.AppendFormat(" AND '{0}'>=CONVERT(varchar(100),end_date,23) ", param.end_date);
            return Helper.ToInt(HCQ2_Common.SQL.SqlHelper.ExecuteScalar(sb.ToString()));
        }
    }
}
